package com.zxb.base.autoform.controller;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.zxb.core.common.C;
import com.zxb.core.common.R;
import com.zxb.core.dao.autoform.*;
import com.zxb.core.util.IdUtil;
import com.zxb.core.util.JsonUtil;
import com.zxb.base.autoform.enm.ColumnCompEnum;
import com.zxb.base.autoform.enm.LayoutTypeEnum;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.sql.Timestamp;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author Zxb
 */
@RestController
@RequiredArgsConstructor
public class DataController {
    private final DiyFormDao formDao;
    private final DiyColumnDao columnDao;
    private final DiyLayoutDao layoutDao;
    private final DiyConditionDao conditionDao;
    private final NamedParameterJdbcTemplate jdbcTemplate;

    @PostMapping("/table/data/page/{tableCode}")
    public R<?> page(@PathVariable("tableCode") String tableCode,
                     @RequestBody Map<String, Object> map) {
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda()
                .eq(DiyForm::getCode, tableCode));
        if (form == null) {
            return R.error("表单不存在");
        }
        int page = (int) map.getOrDefault("page", 1);
        int pageSize = (int) map.getOrDefault("pageSize", 10);

        //获取过滤器配置
        List<DiyCondition> conditionList = conditionDao.list(
                new QueryWrapper<DiyCondition>()
                        .lambda()
                        .eq(DiyCondition::getFormId, form.getId())
        );

        //过滤器
        StringBuffer conditionSb = new StringBuffer("1=1");
        Map<String, Object> conditionMap = new LinkedHashMap<>();
        if (CollUtil.isNotEmpty(conditionList)) {
            conditionList.stream().forEach(t -> {
                String code = t.getColumnCode().toLowerCase();
                String type = t.getColumnType();
                String notHolder = t.getHasNot() ? "not" : "";
                String columnHolder = StrUtil.isBlank(t.getFun()) ? code : t.getFun().replaceAll("#", code);
                if ("between".equals(t.getType())) {
                    String startCode = code + "_s";
                    String endCode = code + "_e";
                    StringBuffer sb = new StringBuffer("1=1");
                    String startVal = (String) map.get(startCode);
                    if (StrUtil.isNotBlank(startVal)) {
                        sb.append(" and " + columnHolder + " >= :" + startCode);
                        if (ColumnCompEnum.浮点数.name().equals(type)) {
                            conditionMap.put(startCode, Double.valueOf(startVal));
                        } else if (ColumnCompEnum.日期.name().equals(type)) {
                            conditionMap.put(startCode, DateUtil.parse(startVal).toJdkDate());
                        }
                    }
                    String endVal = (String) map.get(endCode);
                    if (StrUtil.isNotBlank(endVal)) {
                        sb.append(" and " + columnHolder + " <= :" + endCode);
                        if (ColumnCompEnum.浮点数.name().equals(type)) {
                            conditionMap.put(endCode, Double.valueOf(endVal));
                        } else if (ColumnCompEnum.日期.name().equals(type)) {
                            conditionMap.put(endCode, DateUtil.parse(endVal).toJdkDate());
                        }
                    }
                    conditionSb.append(" and " + notHolder + " (" + sb + ") ");
                } else {
                    String val = (String) map.get(code);
                    if (StrUtil.isNotBlank(val)) {
                        if ("like".equals(t.getType())) {
                            conditionSb.append(" and " + notHolder + " (" + columnHolder + " like :" + code + ")");
                            conditionMap.put(code, "%" + val + "%");
                        } else if ("startWith".equals(t.getType())) {
                            conditionSb.append(" and " + notHolder + " (" + columnHolder + " like :" + code + ")");
                            conditionMap.put(code, val + "%");
                        } else if ("endWith".equals(t.getType())) {
                            conditionSb.append(" and " + notHolder + " (" + columnHolder + " like :" + code + ")");
                            conditionMap.put(code, "%" + val);
                        } else {
                            conditionSb.append(" and " + notHolder + " (" + columnHolder + " " + t.getType() + " :" + code + ")");
                            if (ColumnCompEnum.浮点数.name().equals(type)) {
                                conditionMap.put(code, Double.valueOf(val));
                            } else if (ColumnCompEnum.日期.name().equals(type)) {
                                conditionMap.put(code, DateUtil.parse(val).toJdkDate());
                            } else {
                                conditionMap.put(code, val);
                            }
                        }
                    }
                }
            });
        }

        String totalSql = " SELECT count(*) FROM " + tableCode + " WHERE " + conditionSb;
        System.out.println("==========>"+totalSql);
        Integer total = jdbcTemplate.queryForObject(totalSql, conditionMap, Integer.class);

        String sql = " SELECT * FROM " + tableCode + " WHERE " + conditionSb;
        List<Map<String, Object>> list = null;
        if (pageSize > total) {
            list = jdbcTemplate.queryForList(sql, conditionMap);
            handleIdProperty(list);
            handleFlowInstProperty(list);
            return R.success(Arrays.asList(total, list));
        }

        // 封装分页sql
        String pageSql;
        // 第一页查询，直接用rowNum即可
        if (page == 1) {
            pageSql = "SELECT * FROM (" + sql + ") WHERE rownum <= :rowNum";
            conditionMap.put("rowNum", pageSize);
        } else {
            pageSql = "SELECT * FROM ( SELECT t.*, ROWNUM rn FROM(" + sql + ") t WHERE ROWNUM <= :endRow ) WHERE rn > :startRow";
            conditionMap.put("startRow", (page - 1) * pageSize);
            conditionMap.put("endRow", page * pageSize);
        }
        list = jdbcTemplate.queryForList(pageSql, conditionMap);
        handleIdProperty(list);
        handleFlowInstProperty(list);
        return R.success(Arrays.asList(total, list));
    }

    private void handleIdProperty(List<Map<String, Object>> list) {
        String KEY = "ID";
        list.stream().forEach(m -> {
            m.put(KEY, String.valueOf(m.get(KEY)));
        });
    }

    private void handleFlowInstProperty(List<Map<String, Object>> list) {

    }

    @PostMapping("/table/data/add/{tableCode}")
    public R add(@PathVariable("tableCode") String tableCode,
                 @RequestBody Map<String, Object> map) {
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda().eq(DiyForm::getCode, tableCode));

        List<String> pubColumnList = Arrays.asList(C.DEFAULT_COLUMNS);

        List<DiyLayout> layoutList = layoutDao.list(new QueryWrapper<DiyLayout>()
                .lambda()
                .eq(DiyLayout::getFormId, form.getId())
                .eq(DiyLayout::getType, LayoutTypeEnum.add.name()));

        List<String> columnList = generateColumnList(layoutList);
        List<String> allColumnList = new ArrayList<>();

        allColumnList.addAll(pubColumnList);
        allColumnList.addAll(columnList);
        String flowInstLink = (String) map.get("flowInstLink");
        if (StrUtil.isNotBlank(flowInstLink)) {
            //保存数据归属的流程链路
            allColumnList.add("flow_inst_code");
            map.put("flow_inst_code", flowInstLink);
        }

        String fieldNames = allColumnList.stream()
                .map(t -> t.toLowerCase())
                .collect(Collectors.joining(","));
        String values = allColumnList.stream()
                .map(t -> ":" + t.toLowerCase())
                .collect(Collectors.joining(","));

        String sql = "INSERT INTO " + tableCode + "(" + fieldNames + ") VALUES (" + values + ")";

        map.put("id", IdUtil.nextId());
        map.put("create_by", "zxb");
        map.put("update_by", "zxb");
        map.put("ver", 0);
        map.put("is_del", false);
        //设置模糊匹配字段
        handleSearchStrProperty(form.getId(), map);
        Timestamp now = new Timestamp(System.currentTimeMillis());
        map.put("create_time", now);
        map.put("update_time", now);
        //处理日期格式
        handleDateProperty(layoutList, map);
        jdbcTemplate.update(sql, map);
        //统计数据量
        countData(form);
        return R.success();
    }

    private void handleSearchStrProperty(Long formId, Map<String, Object> map) {
        String flowInstLink = (String) map.get("flowInstLink");
        StringBuffer searchStrSb = new StringBuffer("");
        if (StrUtil.isNotBlank(flowInstLink)) {
            searchStrSb.append(flowInstLink);
        }
        List<DiyColumn> likeColumnList = columnDao.list(new QueryWrapper<DiyColumn>()
                .lambda()
                .eq(DiyColumn::getFormId, formId)
                .eq(DiyColumn::getIsLike, true));
        if (CollUtil.isNotEmpty(likeColumnList)) {
            likeColumnList.stream().forEach(t -> {
                searchStrSb.append(map.get(t.getCode().toLowerCase()));
            });
        }
        map.put("search_str", searchStrSb.toString());
    }


    private void handleDateProperty(List<DiyLayout> list, Map<String, Object> map) {
        list.stream()
                .filter(t -> ColumnCompEnum.日期.name().equals(t.getColumnType()))
                .forEach(t -> {
                    String code = t.getColumnCode().toLowerCase();
                    String strVal = (String) map.get(code);
                    Date sqlDate = DateUtil.parse(strVal).toSqlDate();
                    map.put(code, sqlDate);
                });
    }

    private List<String> generateColumnList(List<DiyLayout> layoutList) {
        List<String> columnList = layoutList.stream()
                .map(t -> t.getColumnCode()).collect(Collectors.toList());
        return columnList;
    }

    @PostMapping("/table/data/update/{tableCode}")
    public R update(@PathVariable("tableCode") String tableCode,
                    @RequestBody Map<String, Object> map) {
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda().eq(DiyForm::getCode, tableCode));
        List<String> pubColumnList = Arrays.asList("update_by", "update_time", "search_str");
        List<DiyLayout> layoutList = layoutDao.list(new QueryWrapper<DiyLayout>()
                .lambda()
                .eq(DiyLayout::getFormId, form.getId())
                .eq(DiyLayout::getType, LayoutTypeEnum.update.name()));
        List<String> columnList = generateColumnList(layoutList);

        List<String> allColumnList = new ArrayList<>();
        allColumnList.addAll(pubColumnList);
        allColumnList.addAll(columnList);
        String fieldNames = allColumnList
                .stream()
                .map(f -> f + "=:" + f.toLowerCase())
                .collect(Collectors.joining(","));

        String sql = " UPDATE " + tableCode + " SET " + fieldNames + ",ver=:newVer WHERE id=:id AND ver=:oldVer ";
        map.put("update_by", "zxb");
        Timestamp now = new Timestamp(System.currentTimeMillis());
        map.put("update_time", now);
        //设置模糊匹配字段
        handleSearchStrProperty(form.getId(), map);
        //乐观锁参数
        int oldVer = (int) map.getOrDefault("ver", 1);
        map.put("oldVer", oldVer);
        map.put("newVer", oldVer + 1);
        //处理日期格式
        handleDateProperty(layoutList, map);
        int result = jdbcTemplate.update(sql, map);
        if (result == 0) {
            return R.error("更新失败，请稍后重试");
        }
        return R.success();
    }

    @PostMapping("/table/data/batch/{tableCode}")
    public R batch(@PathVariable("tableCode") String tableCode,
                   @RequestBody Map<String, Object> map) {
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda().eq(DiyForm::getCode, tableCode));
        List<Long> idList = (List<Long>) map.get("ids");
        if (CollUtil.isEmpty(idList)) {
            return R.error("缺少ids参数");
        }
        List<String> allColumnList = new ArrayList<>();
        List<String> pubColumnList = Arrays.asList("update_by", "update_time");
        allColumnList.addAll(pubColumnList);
        List<DiyLayout> layoutList = layoutDao.list(new QueryWrapper<DiyLayout>()
                .lambda()
                .eq(DiyLayout::getFormId, form.getId())
                .eq(DiyLayout::getType, LayoutTypeEnum.batch.name()));
        List<String> columnList = generateColumnList(layoutList);
        for (String str : columnList) {
            boolean hasColumn = (boolean) map.getOrDefault(str + "_ck", false);
            if (hasColumn) {
                allColumnList.add(str);
            }
        }
        String fieldNames = allColumnList
                .stream()
                .map(f -> f + "=:" + f.toLowerCase())
                .collect(Collectors.joining(","));
        String sql = " UPDATE " + tableCode + " SET " + fieldNames + " WHERE id in (:ids) ";

        map.put("update_by", "zxb");
        Timestamp now = new Timestamp(System.currentTimeMillis());
        map.put("update_time", now);

        //处理日期类型
        layoutList.stream()
                .filter(t -> {
                    String code = t.getColumnCode().toLowerCase();
                    boolean hasColumn = (boolean) map.getOrDefault(code + "_ck", false);
                    return hasColumn && ColumnCompEnum.日期.name().equals(t.getColumnType());
                }).forEach(t -> {
                    String code = t.getColumnCode().toLowerCase();
                    String strVal = (String) map.get(code);
                    Date sqlDate = DateUtil.parse(strVal).toSqlDate();
                    map.put(code, sqlDate);
                });
        for (String str : columnList) {
            boolean hasColumn = (boolean) map.getOrDefault(str + "_ck", false);
            if (hasColumn) {
                allColumnList.add(str);
            }
        }

        int result = jdbcTemplate.update(sql, map);
        if (result == 0) {
            return R.error("更新失败，请稍后重试");
        } else {
            return R.success(result);
        }
    }

    @PostMapping("/table/data/del/{tableCode}")
    public R del(@PathVariable("tableCode") String tableCode,
                 @RequestBody List<Long> ids) {
        String sql = " DELETE FROM " + tableCode + " WHERE id in (:ids) ";
        Map<String, Object> param = new HashMap<>(1);
        param.put("ids", ids);
        jdbcTemplate.update(sql, param);

        //统计数据量
        DiyForm form = formDao.getOne(new QueryWrapper<DiyForm>()
                .lambda().eq(DiyForm::getCode, tableCode));
        if (form != null) {
            countData(form);
        }
        return R.success();
    }

    private void countData(DiyForm form) {
        String countSql = " select count(*) from " + form.getCode() + " where is_del = :isDel ";
        Map<String, Object> countMap = new HashMap<>(1);
        countMap.put("isDel", 0);
        int count = jdbcTemplate.queryForObject(countSql, countMap, Integer.class);
        form.setDataCount(count);
        formDao.updateById(form);
    }

    @GetMapping("/table/data/count")
    public R countData(String tableCode) {
        if (StrUtil.isBlank(tableCode)) {
            return R.success(0);
        }
        try {
            String sql = " select count(*) from " + tableCode + " where is_del = :isDel ";
            Map<String, Object> map = new HashMap<>(1);
            map.put("isDel", 0);
            int count = jdbcTemplate.queryForObject(sql, map, Integer.class);
            return R.success(count);
        } catch (Exception e) {
            return R.success(0);
        }
    }

    @PostMapping("/table/data/valid/{state}")
    public R validSuccess(@PathVariable("state") String state,
                          @RequestBody Map<String, Object> map) {
        String OK = "ok";
        try {
            String param = JsonUtil.OM.writeValueAsString(map);
            System.out.println("==============>接收到参数：");
            System.out.println(param);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (OK.equals(state)) {
            return R.success();
        } else {
            return R.error("远程数据校验失败");
        }
    }
}
